package com.ideal.lx_mss.utils;

import com.ideal.lx_mss.mapper.BasicMapper;
import org.json.JSONArray;
import org.json.JSONObject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.Arrays;
import java.util.List;
import java.util.Set;

@Component
public class SqlUtils {
    @Value("${spring.datasource.url}")
    private String databaseUrl;
    @Value("${spring.datasource.username}")
    private String userName;
    @Value("${spring.datasource.password}")
    private String passWord;
    @Value("${tableNames}")
    private String[] tableNames;

    @Autowired
    private TableFromJsonUtil tableFromJsonUtil;
    @Autowired
    private BasicMapper basicMapper;

    public boolean createSqlTable(String table,String tableTemp){

        String sql = "CREATE TABLE "+tableTemp+" LIKE "+table+";"; //复制表语句
        //查询该表是否存在
        boolean tableExists = tableFromJsonUtil.tableExists(table);
        boolean tableTempExists = tableFromJsonUtil.tableExists(tableTemp);
        if (!tableExists){
            //目标表不存在
            return false;
        }

        if (tableTempExists){
            //目标表临时表已存在
            sql = "TRUNCATE TABLE "+tableTemp+";";
            return true;
        }

        try {
            Connection conn = DriverManager.getConnection(databaseUrl, userName, passWord);
            Statement stmt = conn.createStatement();

            // 执行 SQL 创建表语句
            stmt.execute(sql);

            // 关闭连接
            stmt.close();
            conn.close();


        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }


        return true;
    }


    /**
     * 清空表
     */
    public void truncateTable(){
        for (String tableName : tableNames) {
            basicMapper.truncateTables(tableName);
        }
    }

    /**
     * 根据json拼sql语句
     * @param tableName
     * @param jsonData
     * @return
     */
    public String generateInsertSQL(String tableName, JSONObject jsonData) {
        StringBuilder fields = new StringBuilder();
        StringBuilder values = new StringBuilder();

        // 使用names方法获取所有的键
        JSONArray keys = jsonData.names();
        if (keys != null) {
            for (int i = 0; i < keys.length(); i++) {
                String key = keys.getString(i);
                fields.append(key).append(",");
                // 假设你的value是基本类型或者String，如果存在其他复杂类型，需要进一步转换处理
                Object value = jsonData.get(key);
                // 检查值是否为 String 类型，并进行适当的转义处理
                if (value instanceof String) {
                    // Escaping single quotes and backslashes in the string value
                    String escapedValue = value.toString().replace("\\", "\\\\").replace("'", "''");
                    value = "'" + escapedValue + "'";
                }
                values.append(value).append(",");
            }
        }

        // 删除拼接字符串末尾的逗号
        if (fields.length() > 0) fields.deleteCharAt(fields.length() - 1);
        if (values.length() > 0) values.deleteCharAt(values.length() - 1);

        // 构造INSERT SQL语句
        String sql = "INSERT INTO " + tableName + " (" + fields.toString() + ") VALUES (" + values.toString() + ");";
        return sql;
    }


    public  String buildDevelopInsertSelectQuery(
            String sourceSchema,
            String sourceTable,
            String targetTable,
            String[] columns)
    {
        // 假设columns中的字段名称适用于两个表
        StringBuilder columnList = new StringBuilder();
        String delimiter = "";
        for (String column : columns) {
            columnList.append(delimiter);
            columnList.append("`").append(column).append("`");
            delimiter = ", ";
        }

        // 构造完整的SQL语句
        String query = String.format(
                "INSERT INTO `%s`.`%s` (%s) SELECT %s FROM `%s`.`%s`",
                sourceSchema, targetTable, columnList.toString(),
                columnList.toString(), sourceSchema, sourceTable
        );

        return query;
    }

    // 主函数，用来做demo展示如何使用这个工具类
    public static void main(String[] args) {
        String[] columns = { "log_id", "method", "model", "level", "topic", "table_name", "remark", "create_time", "form_id" };

        // 传入目标schema和表名以及来源schema和表名
//        String sql = buildInsertSelectQuery("mss_kafka", "tem2", "mss_kafka", "temp1", columns);

//        System.out.println(sql);
    }
}
